/*
 * This file is released under the terms of the Artistic License.  Please see
 * the file LICENSE, included in this package, for details.
 *
 * Copyright (C) 2006 Rilson Nascimento
 *
 * Trade Status transaction
 * ------------------------
 * This transaction returns the status of fifty trades. A list of all trades
 * for a given customer's account is retrieved and the fifty most recently
 * placed trades are selected.
 *
 * Based on TPC-E Standard Specification Draft Revision 0.32.2c Clause 3.3.5.
 */


/*
 * Frame 1
 * responsible for retrieving information on the 50 most recent trades
 */

CREATE OR REPLACE FUNCTION TradeStatusFrame1 (
        IN acct_id BIGINT,
        OUT broker_name VARCHAR(100),
        OUT charge NUMERIC(10, 2)[],
        OUT cust_f_name VARCHAR(30),
        OUT cust_l_name VARCHAR(30),
        OUT ex_name VARCHAR(100)[],
        OUT exec_name VARCHAR(64)[],
        OUT num_found BIGINT,
        OUT s_name VARCHAR(70)[],
        OUT st_name VARCHAR(10)[],
        OUT symbol VARCHAR(15)[],
        OUT trade_dts TIMESTAMP[],
        OUT trade_id BIGINT[],
        OUT trade_qty INTEGER[],
        OUT type_name VARCHAR(12)[]
) RETURNS SETOF record AS $$
DECLARE
	-- variables
    i  INTEGER;
	rs RECORD;
BEGIN
	-- Only want 50 rows, the 50 most recent trades for this customer account
    i = 0;
	FOR rs IN
		SELECT
			TR.T_ID,
            TR.T_DTS,
            ST.ST_NAME,
            TT.TT_NAME,
            TR.T_S_SYMB,
		    TR.T_QTY,
            TR.T_EXEC_NAME,
            TR.T_CHRG,
            SEC.S_NAME,
            EX.EX_NAME
		FROM
            TRADE TR,
			STATUS_TYPE ST,
			TRADE_TYPE TT,
			SECURITY SEC,
			EXCHANGE EX
		WHERE	
            TR.T_CA_ID = acct_id AND
			ST.ST_ID = TR.T_ST_ID AND
			TT.TT_ID = TR.T_TT_ID AND
			SEC.S_SYMB = TR.T_S_SYMB AND
			EX.EX_ID = SEC.S_EX_ID
		ORDER BY T_DTS desc
		LIMIT 50
	LOOP
        i = i + 1;
        trade_id[i]  = rs.T_ID;
        trade_dts[i] = rs.T_DTS;
        st_name[i]   = rs.ST_NAME;
        type_name[i] = rs.TT_NAME;
        symbol[i]    = rs.T_S_SYMB;
        trade_qty[i] = rs.T_QTY;
        exec_name[i] = rs.T_EXEC_NAME;
        charge[i]    = rs.T_CHRG;
        s_name[i]    = rs.S_NAME;
        ex_name[i]   = rs.EX_NAME;
	END LOOP;

    num_found = i;

	SELECT
        C_L_NAME,
		C_F_NAME,
		B_NAME
	INTO
        cust_l_name,
		cust_f_name,
		broker_name
	FROM
        CUSTOMER_ACCOUNT,
		CUSTOMER,
		BROKER
	WHERE
        CA_ID = acct_id AND
		C_ID = CA_C_ID AND
		B_ID = CA_B_ID;

    RETURN NEXT;
END;
$$ LANGUAGE 'plpgsql';
